package gwtappcontainer.server.apps.insight;

import gwtappcontainer.server.Utils;
import gwtappcontainer.server.apps.APIException;
import gwtappcontainer.shared.apis.APIResponse.Status;
import gwtappcontainer.shared.apps.insight.Practise;
import gwtappcontainer.shared.apps.insight.ProgramType;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Collections;

public class ProgramTypeRepository {
	
	public static ProgramType get(String programTypeName) {
        
	    try {
	    	String sql = "select program_type_id, program_type from program_types where program_type = ?";            
           
            ProgramType programType = null;
           
            try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {                   
                try (PreparedStatement ps = connection.prepareStatement(sql)) {                       
                    ps.setString(1, programTypeName.toLowerCase()); 
                    
                    try (ResultSet resultSet = ps.executeQuery()) {                    	
                        while (resultSet.next()) {                        	
                    		programType = new ProgramType(); 
                    		programType.id = resultSet.getInt(1);
                    		programType.name = resultSet.getString(2);                        		                        	                        	                        	
                        }
                    }                                        
                }
                
                if (null == programType)
                	return null;
                
                sql = "select practise from practises where practise_id in (select practise_id from program_type_practises where program_type_id = ?) order by practise";
                try (PreparedStatement ps = connection.prepareStatement(sql)) {                       
                    ps.setInt(1, programType.id); 
                    
                    try (ResultSet resultSet = ps.executeQuery()) {                    	
                        while (resultSet.next()) {                        	                    		                    		                        	                        
                        	programType.practises.add(resultSet.getString(1));
                        }
                    }                                        
                }
                
                return programType;                                                           
            }                      	          
	    } catch (Exception ex) {
	            throw new RuntimeException(ex);
	    }              
	}
	
	public static ProgramType add(String programTypeName) {
					
		 if (null != get(programTypeName))
			 throw new APIException(Status.ERROR_RESOURCE_ALREADY_EXISTS, 
					 "Program type [" + programTypeName + "] already exists");
		 
		 try {
		     String sql = "insert into program_types values (0, ?)";                      
		    
		     try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
		            
		         try (PreparedStatement ps = connection.prepareStatement(sql)) {
		                
		             ps.setString(1, programTypeName.toLowerCase());
		            
		             ps.executeUpdate(); 		            
		         }
		     }
		    
		     ProgramType programType = get(programTypeName);      
		     return programType;
		     
		 } catch (Exception ex) {
		     throw new RuntimeException(ex);
		 }              
	 }

	public static void delete(String programTypeName) {
         
	     ProgramType programType = get(programTypeName);
	    
	     if (null == programType)
	         throw new APIException(Status.ERROR_RESOURCE_DOES_NOT_EXIST,
	                         "Program type [" + programTypeName + "] does not exist");
	                                            
	     try {
	         String sql = "delete from program_types where program_type = ?";                        
	        
	         try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
	                
	             try (PreparedStatement ps = connection.prepareStatement(sql)) {
	                    
	                 ps.setString(1, programTypeName.toLowerCase());
	                
	                 ps.executeUpdate();                                    
	             }
	         }                                                                      
	     } catch (Exception ex) {
	         throw new RuntimeException(ex);
	     }              
	 }

	public static ArrayList<ProgramType> getAll() {

		try {
			String sql = "select program_type_id, program_type from program_types order by program_type";
	       
	        ArrayList<ProgramType> all = new ArrayList<>();
	       
	        try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {
	               	        	
	            try (PreparedStatement ps = connection.prepareStatement(sql)) {	                   	               
	               
		            try (ResultSet resultSet = ps.executeQuery()) {                                        
		                while (resultSet.next()) {
		                	ProgramType programType = new ProgramType();
		                	programType.id = resultSet.getInt(1);
		                	programType.name = resultSet.getString(2);
		                	
		                    all.add(programType);
		                }
		            }
	            }
	        }
	       
	        return all;
	           
	    } catch (Exception ex) {
	            throw new RuntimeException(ex);
	    } 
	}

	public static ProgramType addPractise(String programTypeName, String practiseName) {
		ProgramType programType = get(programTypeName);
		if (null == programType) 
			throw new APIException(Status.ERROR_RESOURCE_DOES_NOT_EXIST, 
					"Program type [" + programTypeName + "] does not exist");
		
		Practise practise = PractiseRepository.get(practiseName);
		if (null == practise) 
			throw new APIException(Status.ERROR_RESOURCE_DOES_NOT_EXIST, 
					"Practise [" + practiseName + "] does not exist");
		
		practiseName = practiseName.toLowerCase();
		if (programType.practises.contains(practiseName))
			return programType;
		
		try {
		     String sql = "insert into program_type_practises values (?, ?)";                      
		    
		     try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {		            
		         try (PreparedStatement ps = connection.prepareStatement(sql)) {		                
		             ps.setLong(1, programType.id);
		             ps.setLong(2, practise.id);
		            
		             ps.executeUpdate();                                    
		         }
		     }
		     
		     programType.practises.add(practiseName.toLowerCase());
		     Collections.sort(programType.practises);
		    		         
		     return programType;		       
		 } catch (Exception ex) {
		     throw new RuntimeException(ex);
		 }   										
	}
	
	public static ProgramType deletePractise(String programTypeName, String practiseName) {
		ProgramType programType = get(programTypeName);
		if (null == programType) 
			throw new APIException(Status.ERROR_RESOURCE_DOES_NOT_EXIST, 
					"Program type [" + programTypeName + "] does not exist");
		
		Practise practise = PractiseRepository.get(practiseName);
		if (null == practise) 
			throw new APIException(Status.ERROR_RESOURCE_DOES_NOT_EXIST, 
					"Practise [" + practiseName + "] does not exist");
		
		practiseName = practiseName.toLowerCase();
		if (! programType.practises.contains(practiseName))
			return programType;
		
		try {
		     String sql = "delete from program_type_practises where program_type_id = ? and practise_id = ?";                      
		    
		     try (Connection connection = DriverManager.getConnection(Utils.getCloudSqlURL())) {		            
		         try (PreparedStatement ps = connection.prepareStatement(sql)) {		                
		             ps.setLong(1, programType.id);
		             ps.setLong(2, practise.id);		           
		            
		             ps.executeUpdate();                                    
		         }
		     }
		     
		     programType.practises.remove(practiseName.toLowerCase());		    		    
		     return programType;		        
		 } catch (Exception ex) {
		     throw new RuntimeException(ex);
		 }   										
	}
}

